﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class AccountDAL
    {

        #region[Account_SelectALL]
        public List<DTO.AccountDTO> Account_SelectALL()
        {
            List<DTO.AccountDTO> accall = new List<DTO.AccountDTO>();
            DTO.AccountDTO acc = new DTO.AccountDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllAccount", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                acc.AccountID = dr.GetInt32(0);
                acc.Username = dr.GetString(1);
                acc.Password = dr.GetString(2);
                acc.Name = dr.GetString(3);
                acc.Phone = dr.GetString(4);
                acc.Type = dr.GetInt32(5);
                accall.Add(acc);
            }
            dr.Close();
            con.Close();

            return accall;
        }
        #endregion

        #region[Account_SelectByName]
        public List<DTO.AccountDTO> Account_SelectByName(String name)
        {
            List<DTO.AccountDTO> accname = new List<DTO.AccountDTO>();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccountByName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@name", name);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.AccountDTO acc = new DTO.AccountDTO();
                acc.AccountID = dr.GetInt32(0);
                acc.Username = dr.GetString(1);
                acc.Password = dr.GetString(2);
                acc.Name = dr.GetString(3);
                acc.Phone = dr.GetString(4);
                acc.Type = dr.GetInt32(5);
                accname.Add(acc);
            }
            dr.Close();
            con.Close();
            return accname;
        }
        #endregion

        #region[Account_SelectByID]
        public DTO.AccountDTO Custummer_SelectByID(int id)
        {
            DTO.AccountDTO acc = new DTO.AccountDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccountByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@accountid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                acc.AccountID = dr.GetInt32(0);
                acc.Username = dr.GetString(1);
                acc.Password = dr.GetString(2);
                acc.Name = dr.GetString(3);
                acc.Phone = dr.GetString(4);
                acc.Type = dr.GetInt32(5);
            }
            dr.Close();
            con.Close();
            return acc;
        }
        #endregion

        #region[Account_SelectByUserName]
        public List<DTO.AccountDTO> Custummer_SelectByUserName(string username)
        {
            List<DTO.AccountDTO> accusname = new List<DTO.AccountDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAccountByUserName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@username", username);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.AccountDTO acc = new DTO.AccountDTO();
                acc.AccountID = dr.GetInt32(0);
                acc.Username = dr.GetString(1);
                acc.Password = dr.GetString(2);
                acc.Name = dr.GetString(3);
                acc.Phone = dr.GetString(4);
                acc.Type = dr.GetInt32(5);
                accusname.Add(acc);
            }
            dr.Close();
            con.Close();
            return accusname;
        }
        #endregion

        #region [Account_Insert]
        public bool Account_Insert(DTO.AccountDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddAccount", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@username", acc.Username));
            cmd.Parameters.Add(new MySqlParameter("@password", acc.Password));
            cmd.Parameters.Add(new MySqlParameter("@name", acc.Name));
            cmd.Parameters.Add(new MySqlParameter("@phone", acc.Phone));
            cmd.Parameters.Add(new MySqlParameter("@type", acc.Type));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Account_Update]
        public bool Account_Update(DTO.AccountDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("UpdateAccount", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@username", acc.Username));
            cmd.Parameters.Add(new MySqlParameter("@password", acc.Password));
            cmd.Parameters.Add(new MySqlParameter("@name", acc.Name));
            cmd.Parameters.Add(new MySqlParameter("@phone", acc.Phone));
            cmd.Parameters.Add(new MySqlParameter("@type", acc.Type));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Account_Delete]
        public bool Account_DeleteByID(DTO.AccountDTO acc)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("DeleteAccountByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@accountid", acc.AccountID));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

    }
}
